#!/usr/bin/python
#coding=utf-8

#author   : mayongfan
#version  : 1.0
#datetime : 2023.12.11

import os
import time
import requests
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Font, Fill
from openpyxl.styles.colors import RED
import pandas as pd
import numpy as np

fontRed = Font(color=RED, bold=True)
fontBold = Font(bold=True)

redBalls = []
blueBalls = []
omitTable = [[-1 for col in range(34)] for row in range(2000)]
omitTable_blue = [[-1 for col in range(17)] for row in range(2000)]

arrSeral = []
arrDate = []
startSeral = 0

"获取网络数据"
"sNo: 最近期数"
"sNo=0: 为输入截止日期方法查询"
def getData(sNo):
    global arrSeral, arrDate
    headers ={
        'User-Agent': 'Mozilla/5.0(X11;Linux aarch64)AppleWebKit/537.36(KHTML, likeGecko) Chrome / 77.0.3770.100Safari / 537.36'
    }
    url = 'https://www.cwl.gov.cn/cwl_admin/front/cwlkj/search/kjxx/findDrawNotice?name=ssq'
    url += '&issueCount=' + str(sNo) + '&issueStart=&issueEnd=&dayStart=&dayEnd=&pageNo=1&pageSize='+str(sNo)+'&week=&systemType=PC'
    response = requests.get(url,headers=headers)
    # print(response)
    json_data = response.json()
    for r in json_data['result']:
        arrSeral.append(r['code'])
        arrDate.append(r['date'])
        integers_red = [int(item) for item in r['red'].split(',')]
        integers_blue = int(r['blue'])
        redBalls.append(integers_red)
        blueBalls.append(integers_blue)
    setOmitTable()
    setOmitTable_blue()
def setOmitTable():
    for i in range(1, 34): # 1-33号码
        omit = 0
        for j in range(99, -1, -1):
            for k in range(0, 6):
                if redBalls[j][k] == i:
                    omit = 0
            omitTable[j][i] = omit
            omit = omit + 1
def setOmitTable_blue():
    for i in range(1, 17): # 1-33号码
        omit = 0
        for j in range(99, -1, -1):
            if blueBalls[j] == i:
                omit = 0
            omitTable_blue[j][i] = omit
            omit = omit + 1
def applyXlsFont(ws):
    rowNum = ws.max_row
    ws['A%d' % rowNum].font = fontBold
    ws['B%d' % rowNum].font = fontBold
    ws['C%d' % rowNum].font = fontBold
    ws['D%d' % rowNum].font = fontBold
    ws['E%d' % rowNum].font = fontBold
    ws['F%d' % rowNum].font = fontBold
    ws['G%d' % rowNum].font = fontBold
    ws['H%d' % rowNum].font = fontBold
    ws['I%d' % rowNum].font = fontBold
    ws['J%d' % rowNum].font = fontBold
    ws['K%d' % rowNum].font = fontBold
    ws['L%d' % rowNum].font = fontRed
    ws['M%d' % rowNum].font = fontBold
    ws['N%d' % rowNum].font = fontBold
    ws['O%d' % rowNum].font = fontBold
    ws['P%d' % rowNum].font = fontBold
    ws['Q%d' % rowNum].font = fontBold
    ws['R%d' % rowNum].font = fontBold
    ws['S%d' % rowNum].font = fontBold
    ws['T%d' % rowNum].font = fontBold
    ws['U%d' % rowNum].font = fontBold
"1.奇偶数"
def fileOddOrEven(ws):
    global arrDate, arrSeral
    ws.append([''])
    ws.append([arrDate[startSeral] + arrSeral[startSeral]])
    applyXlsFont(ws)
    oddOrEven(ws, 10)
    oddOrEven(ws, 9)
    oddOrEven(ws, 8)
    oddOrEven(ws, 7)
    oddOrEven(ws, 6)
    oddOrEven(ws, 5)
    oddOrEven(ws, 1)
    oddOrEven_blue(ws, 60)
    oddOrEven_blue(ws, 30)
def oddOrEven(ws, sNo):
    odd = 0
    even = 0
    for i in range(startSeral, startSeral + sNo):
        for j in range(0, 6):
            if (redBalls[i][j] % 2 == 0):
                even = even + 1
            else:
                odd = odd + 1

    if (sNo != 1):
        if (even > odd):
            ws.append(['近' + str(sNo) + '期有', '奇=' + str(odd) + '，偶=' + str(even), '偶+' + str(even - odd)])
            applyXlsFont(ws)
        elif (even < odd):
            ws.append(['近' + str(sNo) + '期有', '奇=' + str(odd) + '，偶=' + str(even), '奇+' + str(odd - even)])
            applyXlsFont(ws)
        else:
            ws.append(['近' + str(sNo) + '期有', '奇=' + str(odd) + '，偶=' + str(even), '奇=偶'])
            applyXlsFont(ws)
    else:
        ws.append(['本期奇偶比例:', '奇 : 偶 = %d : %d' % (odd, even)])
        applyXlsFont(ws)
def oddOrEven_blue(ws, sNo):
    odd = 0
    even = 0
    for i in range(startSeral, startSeral + sNo):
        if (blueBalls[i] % 2 == 0):
            even = even + 1
        else:
            odd = odd + 1

    if (even > odd):
        ws.append(['蓝球近' + str(sNo) + '期有', '奇=' + str(odd) + '，偶=' + str(even), '偶+' + str(even - odd)])
        applyXlsFont(ws)
    elif (even < odd):
        ws.append(['蓝球近' + str(sNo) + '期有', '奇=' + str(odd) + '，偶=' + str(even), '奇+' + str(odd - even)])
        applyXlsFont(ws)
    else:
        ws.append(['蓝球近' + str(sNo) + '期有', '奇=' + str(odd) + '，偶=' + str(even), '奇=偶'])
        applyXlsFont(ws)
"2.大小数"
def fileBigOrSmall(ws):
    global arrDate, arrSeral
    ws.append([''])
    ws.append([arrDate[startSeral] + arrSeral[startSeral]])
    applyXlsFont(ws)
    bigOrSmall(ws, 10)
    bigOrSmall(ws, 9)
    bigOrSmall(ws, 8)
    bigOrSmall(ws, 7)
    bigOrSmall(ws, 6)
    bigOrSmall(ws, 5)
    bigOrSmall(ws, 1)
    bigOrSmall_blue(ws, 60)
    bigOrSmall_blue(ws, 30)
def bigOrSmall(ws, sNo):
    big = 0
    small = 0
    for i in range(startSeral, startSeral + sNo):
        for j in range(0, 6):
            if (redBalls[i][j] <= 16):
                small = small + 1
            else:
                big = big + 1

    if (sNo != 1):
        if (small > big):
            ws.append(['近' + str(sNo) + '期有', '大=' + str(big) + '，小=' + str(small), '小+' + str(small - big)])
            applyXlsFont(ws)
        elif (small < big):
            ws.append(['近' + str(sNo) + '期有', '大=' + str(big) + '，小=' + str(small), '大+' + str(big - small)])
            applyXlsFont(ws)
        else:
            ws.append(['近' + str(sNo) + '期有', '大=' + str(big) + '，小=' + str(small), '大=小'])
            applyXlsFont(ws)
    else:
        ws.append(['本期大小比例:', '大 : 小 = %d : %d' % (big, small)])
        applyXlsFont(ws)
def bigOrSmall_blue(ws, sNo):
    big = 0
    small = 0
    for i in range(startSeral, startSeral + sNo):
        if (blueBalls[i] <= 8):
            small = small + 1
        else:
            big = big + 1

    if (small > big):
        ws.append(['蓝球近' + str(sNo) + '期有', '大=' + str(big) + '，小=' + str(small), '小+' + str(small - big)])
        applyXlsFont(ws)
    elif (small < big):
        ws.append(['蓝球近' + str(sNo) + '期有', '大=' + str(big) + '，小=' + str(small), '大+' + str(big - small)])
        applyXlsFont(ws)
    else:
        ws.append(['蓝球近' + str(sNo) + '期有', '大=' + str(big) + '，小=' + str(small), '大=小'])
        applyXlsFont(ws)
"3.和值偏差"
def sumOffset(ws):
    global arrDate, arrSeral
    sum = 0
    for i in range(0, 6):
        sum += redBalls[startSeral][i]
    cells = ['' for row in range(21)]
    cells[0] = arrDate[startSeral]
    cells[1] = arrSeral[startSeral]
    cells[2] = str(sum)
    cells[11] = '+'

    place = int(sum / 10)
    # sum: cells[place + 1]  100: 11
    if place > 10:
        for i in range(12, place + 1 + 1):
            cells[i] = '+'
    elif place < 10:
        for i in range(place + 1, 11):
            cells[i] = '+'
    ws.append(cells)
    applyXlsFont(ws)
"4.遗漏"
def fileOmit(ws):
    global arrDate, arrSeral
    cells = ['  ' for row in range(8)]
    cells[0] = arrDate[startSeral]
    cells[1] = arrSeral[startSeral]
    for i in range(0, 6):
        cells[2] += '  %02d' % (redBalls[startSeral][i])

    o = omit(startSeral)

    omitNum = 0  # 小于10的个数
    omitSum = 0  # 总和
    for i in range(0, 6):
        if o[i] < 10:
            omitNum = omitNum + 1
        cells[3] += '  %02d' % (o[i])
        omitSum = omitSum + o[i]

    cells[4] = str(omitNum)
    cells[5] = str(omitSum)
    cells[6] = str(float('%.1f' % (omitSum / 6.0)))
    o_blue = omit_blue(startSeral)
    cells[7] += '  %d' % (o_blue)
    ws.append(cells)
    applyXlsFont(ws)

    rowNum = ws.max_row
    numCell = ws['E%d' % rowNum]
    if numCell.value == '6':
        numCell.font = fontRed
def omit(start):
    o = [-1 for col in range(6)] #遗漏次数
    for i in range(0, 6):
        for j in range(start + 1, 99):
            for k in range(0, 6):
                if redBalls[j][k] == redBalls[start][i]:
                    o[i] = j - start - 1
                    break
            if o[i] != -1:
                break
    return o
def omit_blue(start):
    o = [-1] #蓝球遗漏次数
    for j in range(start + 1, 99):
        if blueBalls[j] == blueBalls[start]:
            o = j - start - 1
            break

    return o
"5.区间"
def ballRange(ws):
    print('正在运行区间')
    global arrDate, arrSeral
    cells = ['' for row in range(17)]
    cells[0] = arrDate[startSeral]
    cells[1] = arrSeral[startSeral]

    bRange_7 = [0 for col in range(7)]
    for i in range(0, 6):
        if redBalls[startSeral][i] >= 1 and redBalls[startSeral][i] <= 5:
            bRange_7[0] = bRange_7[0] + 1
        elif redBalls[startSeral][i] >= 6 and redBalls[startSeral][i] <= 10:
            bRange_7[1] = bRange_7[1] + 1
        elif redBalls[startSeral][i] >= 11 and redBalls[startSeral][i] <= 15:
            bRange_7[2] = bRange_7[2] + 1
        elif redBalls[startSeral][i] >= 16 and redBalls[startSeral][i] <= 20:
            bRange_7[3] = bRange_7[3] + 1
        elif redBalls[startSeral][i] >= 21 and redBalls[startSeral][i] <= 25:
            bRange_7[4] = bRange_7[4] + 1
        elif redBalls[startSeral][i] >= 26 and redBalls[startSeral][i] <= 30:
            bRange_7[5] = bRange_7[5] + 1
        elif redBalls[startSeral][i] >= 31 and redBalls[startSeral][i] <= 33:
            bRange_7[6] = bRange_7[6] + 1

    bRange_4 = [0 for col in range(4)]
    for i in range(0,6):
        if redBalls[startSeral][i] >= 1 and redBalls[startSeral][i] <= 8:
            bRange_4[0] = bRange_4[0] + 1
        elif redBalls[startSeral][i] >= 9 and redBalls[startSeral][i] <= 16:
            bRange_4[1] = bRange_4[1] + 1
        elif redBalls[startSeral][i] >= 17 and redBalls[startSeral][i] <= 24:
            bRange_4[2] = bRange_4[2] + 1
        elif redBalls[startSeral][i] >= 25 and redBalls[startSeral][i] <= 33:
            bRange_4[3] = bRange_4[3] + 1
    # 蓝球区间
    aRange = [0 for col in range(4)]
    if blueBalls[startSeral] >= 1 and blueBalls[startSeral] <= 4:
        aRange[0] = aRange[0] + 1
    elif blueBalls[startSeral] >= 5 and blueBalls[startSeral] <= 8:
        aRange[1] = aRange[1] + 1
    elif blueBalls[startSeral] >= 8 and blueBalls[startSeral] <= 12:
        aRange[2] = aRange[2] + 1
    elif blueBalls[startSeral] >= 13 and blueBalls[startSeral] <= 16:
        aRange[3] = aRange[3] + 1

    for i in range(0, 7):
        if bRange_7[i] == 0:
            cells[i + 2] = '-'
        else:
            cells[i + 2] = str(bRange_7[i])

    # 蓝球
    for i in range(0, 4):
        if aRange[i] == 0:
            cells[i + 9] = '-'
        else:
            cells[i + 9] = str(aRange[i])
    # 8个数字分区
    for i in range(0, 4):
        if bRange_4[i] == 0:
            cells[i + 13] = '-'
        else:
            cells[i + 13] = str(bRange_4[i])
    ws.append(cells)
    applyXlsFont(ws)
"6.百分比表, 热号，冷号，温号"
def hotOrCold(ws):
    print('正在运行百分比表')
    global arrDate, arrSeral
    nums = [0 for row in range(33)]
    nums_percent = [0 for row in range(33)]
    cells = ['' for row in range(16)]
    cells[0] = arrDate[startSeral]
    cells[1] = arrSeral[startSeral]
    for i in range(startSeral, startSeral + 5):
        for j in range(0, 6):
            if nums[redBalls[i][j] - 1] == 0:
                nums[redBalls[i][j] - 1] = 1 #近1-5期出现
    for i in range(startSeral + 5, startSeral + 10):
        for j in range(0, 6):
            if nums[redBalls[i][j] - 1] == 0:
                nums[redBalls[i][j] - 1] = 2 #近6-10期出现
            elif nums[redBalls[i][j] -1] == 1:
                nums[redBalls[i][j] - 1] = 3 #两个区间都出现
    percentageNum = ''
    nextInPercentCount = 0
    hot = ''
    cold = ''
    warm = ''
    hNum = 0
    cNum = 0
    wNum = 0
    nextinhNum = 0
    nextincNum = 0
    nextinwNum = 0
    for i in range(0, 33):
        if np.count_nonzero(np.array(redBalls[startSeral:startSeral+10]) == i+1) >= 2 and np.count_nonzero(np.array(redBalls[startSeral:startSeral+5]) == i+1)>=1:
            percentageNum += '  %02d' % (i + 1)
            if nums_percent[i] == 0:
                nums_percent[i] = 1
        if nums[i] == 0:
            cold += '  %02d' % (i + 1)
            cNum = cNum + 1
            if (cNum >= 10):
                cNum = 0
                #cold += '\n'
        elif nums[i] == 3:
            hot += '  %02d' % (i + 1)
            hNum = hNum + 1
            if (hNum >= 10):
                hNum = 0
                #hot += '\n'
        else:
            warm += '  %02d' % (i + 1)
            wNum = wNum + 1
            if (wNum >= 10):
                wNum = 0
                #warm += '\n'
    # 确定下期中奖号码中冷热温号数目
    for j in range(0, 6):
        if nums[redBalls[startSeral - 1][j] - 1] == 3:
            nextinhNum += 1
        elif nums[redBalls[startSeral - 1][j] - 1] == 0:
            nextincNum += 1
        else:
            nextinwNum += 1
    for j in range(0,6):
        if nums_percent[redBalls[startSeral - 1][j] - 1] == 1:
            nextInPercentCount += 1
    #蓝球的百分比
    nums_blue = [0 for row in range(16)]
    for i in range(startSeral, startSeral + 15):
        if nums_blue[blueBalls[i] - 1] == 0:
            nums_blue[blueBalls[i] - 1] = 1 #近15期出现
    for i in range(startSeral + 15, startSeral + 30):
        if nums_blue[blueBalls[i] - 1] == 0:
            nums_blue[blueBalls[i] - 1] = 2 #近15-30期出现
        elif nums_blue[blueBalls[i] - 1] == 1:
            nums_blue[blueBalls[i] - 1] = 3 #两个区间都出现
    hot_blue = ''
    cold_blue = ''
    warm_blue = ''
    hNum_blue = 0
    cNum_blue = 0
    wNum_blue = 0
    nextinhNum_blue = 0
    nextincNum_blue = 0
    nextinwNum_blue = 0
    for i in range(0, 16):
        if nums_blue[i] == 0:
            cold_blue += '  %02d' % (i + 1)
            cNum_blue = cNum_blue + 1
            if (cNum_blue >= 10):
                cNum_blue = 0
                # cold += '\n'
        elif nums_blue[i] == 3:
            hot_blue += '  %02d' % (i + 1)
            hNum_blue = hNum_blue + 1
            if (hNum_blue >= 10):
                hNum_blue = 0
                # hot += '\n'
        else:
            warm_blue += '  %02d' % (i + 1)
            wNum_blue = wNum + 1
            if (wNum_blue >= 10):
                wNum_blue = 0
                # warm += '\n'
    # 确定下期中奖号码中冷热温号数目
    if nums_blue[blueBalls[startSeral - 1] - 1] == 3:
        nextinhNum_blue += 1
    elif nums_blue[blueBalls[startSeral - 1] - 1] == 0:
        nextincNum_blue += 1
    else:
        nextinwNum_blue += 1
    cells[2] = hot
    cells[3] = cold
    cells[4] = warm
    cells[5] = nextinhNum  # 下期中奖号码在热号中的个数
    cells[6] = nextinwNum  # 下期中奖号码在温号中的个数
    cells[7] = nextincNum  # 下期中奖号码在冷号中的个数
    cells[8] = hot_blue
    cells[9] = cold_blue
    cells[10] = warm_blue
    cells[11] = nextinhNum_blue  # 下期蓝球中奖号码在热号中的个数
    cells[12] = nextinwNum_blue  # 下期蓝球中奖号码在温号中的个数
    cells[13] = nextincNum_blue  # 下期蓝球中奖号码在冷号中的个数
    cells[14] = percentageNum  # 近10期出现2次以上且5期内出现1次及以上的数字
    cells[15] = nextInPercentCount  # 下期中奖号码在当中的个数
    ws.append(cells)
    applyXlsFont(ws)
"7.遗漏数字偏差表"
def omitDict(start, o):
    out = False
    for i in range(0, 6):
        for j in range(start + 1, 99):
            for k in range(0, 6):
                if redBalls[j][k] == redBalls[start][i]:
                    o[redBalls[start][i]] = j - start - 1
                    out = True
                    break
            if out:
                out = False
                break
    return o
def omitDict_blue(start, o):
    out = False
    for j in range(start + 1, 99):
        if blueBalls[j] == blueBalls[start]:
            o[blueBalls[start]] = j - start - 1
            out = True
            break
        if out:
            out = False
            break
    return o
def omitOffset(ws, d):
    global arrDate, arrSeral
    repetition_counts = 0 #记录与上期重复的中奖个数
    last_neighbor_counts = 0 #记录与上期相邻的中奖个数
    neighbor_counts = 0 #记录本期相邻的中奖个数
    redStr = ''
    for i in range(0, 6):
        redStr += '%02d ' % redBalls[startSeral][i]
        for j in range(0, 6):
            if redBalls[startSeral][i] == redBalls[startSeral+1][j]:
                repetition_counts +=1
            if abs(redBalls[startSeral][i]-redBalls[startSeral+1][j]) == 1:
                last_neighbor_counts +=1
    for i in range(5):
        if redBalls[startSeral][i+1]-redBalls[startSeral][i] == 1:
            neighbor_counts +=1
    ws.append(['过去' + str(d) + '期', '', '本期中奖号码: %s' % redStr,'与上期重复中奖号码个数: %s' % repetition_counts,'与上期相邻中奖号码个数: %s' % last_neighbor_counts, '本期相邻中奖号码对数: %s' % neighbor_counts])
    applyXlsFont(ws)
    ws.append(['遗漏次数', '符合个数', '符合数字'])
    applyXlsFont(ws)

    o = {}
    for i in range(startSeral + d - 1, startSeral - 1, -1):
        omitDict(i, o)
    arr = [[] for i in range(6)]
    cnt = {}
    for i in range(0, 6):
        for (k, v) in o.items():
            if v == i:
                arr[i].append(k)
        cnt[i] = len(arr[i])
    dictCnt = sorted(cnt.items(), key=lambda d: d[1], reverse=False)

    for i in range(0, 6):
        diStr = '  '
        for j in range(1, 34):
            if omitTable[startSeral][j] == dictCnt[i][0]:
                diStr += '  %02d' % (j)
        ws.append([dictCnt[i][0], dictCnt[i][1], diStr])
        applyXlsFont(ws)
def omitOffset_blue(ws, d):
    global arrDate, arrSeral
    # 加入蓝球的6倍期数的遗漏统计，因为每一期红球是6个数字，而蓝球只有1个数字
    ws.append(['蓝球过去' + str(d) + '期','本期中奖号码: %s' % blueBalls[startSeral]])
    applyXlsFont(ws)
    ws.append(['蓝球遗漏次数', '蓝球符合个数', '蓝球符合数字'])
    applyXlsFont(ws)

    # 同样的方法统计蓝球，将omitDict函数换为
    o_blue = {}
    for i in range(startSeral + d - 1, startSeral - 1, -1):
        omitDict_blue(i, o_blue)
    arr_blue = [[] for i in range(31)]
    cnt_blue = {}
    for i in range(0, 26):
        for (k, v) in o_blue.items():
            if v == i:
                arr_blue[i].append(k)
        cnt_blue[i] = len(arr_blue[i])
    dictCnt_blue = sorted(cnt_blue.items(), key=lambda d: d[1], reverse=False)
    for i in range(0, 21):
        diStr_blue = '  '
        for j in range(1, 17):
            if omitTable_blue[startSeral][j] == dictCnt_blue[i][0]:
                diStr_blue += '  %02d' % (j)
        ws.append([dictCnt_blue[i][0],dictCnt_blue[i][1],diStr_blue])
        applyXlsFont(ws)
def colorOmitOffset(ws, d):
    global arrDate, arrSeral, startSeral
    if startSeral >= 49:
        return

    startSeral = startSeral + 1
    redStr = ''
    rowNum = ws.max_row - 15
    o = {}
    for i in range(startSeral + d - 1, startSeral - 1, -1):
        omitDict(i, o)
    arr = [[] for i in range(6)]
    cnt = {}
    for i in range(0, 6):
        for (k, v) in o.items():
            if v == i:
                arr[i].append(k)
        cnt[i] = len(arr[i])
    dictCnt = sorted(cnt.items(), key=lambda d: d[1], reverse=False)

    for i in range(0, 6):
        redStr = ''
        for j in range(1, 34):
            if omitTable[startSeral][j] == dictCnt[i][0]:
                for cnt in range(0, 6):
                    if j == redBalls[startSeral - 1][cnt]:
                        redStr += '%02d  ' % j

        ws['F%d' % rowNum].font = fontRed
        ws['F%d' % rowNum].value = redStr
        rowNum = rowNum + 1

    startSeral = startSeral - 1
def fileOmitOffset(ws):
    print('正在运行遗漏数字偏差表')
    global arrDate, arrSeral
    ws.append([''])
    ws.append([arrDate[startSeral] + arrSeral[startSeral]])
    applyXlsFont(ws)
    omitOffset(ws, 5)
    colorOmitOffset(ws, 5)

    # omitOffset(ws, 6)
    # colorOmitOffset(ws, 6)
    # omitOffset(ws, 7)
    # colorOmitOffset(ws, 7)
    # omitOffset(ws, 8)
    # omitOffset(ws, 9)
    # omitOffset(ws, 10)
"尾数偏差追踪表"
def mantissaCalculate(row):
    return [ i % 10 for i in row ]
def mantissaOffset(ws,d):
    global arrDate, arrSeral
    redStr = ''
    for i in range(0, 6):
        redStr += '%02d ' % redBalls[startSeral][i]
    redMantissaNum = 2*(len(mantissaCalculate(redBalls[startSeral])) - len(set(mantissaCalculate(redBalls[startSeral]))))  #计算本期相同尾数数字个数
    ws.append(['过去' + str(d) + '期', '', '本期中奖号码: %s' % redStr,'本期尾数相同个数：%s' % redMantissaNum])
    applyXlsFont(ws)
    ws.append(['号码尾数', '符合个数'])
    applyXlsFont(ws)

    o = {} #用来记录各个尾数的个数
    for i in range(startSeral + d - 1, startSeral - 1, -1):
        m = mantissaCalculate(redBalls[i]) #将本期中奖号码计算成尾数形式
        for j in m:
            if j in o.keys():
                o[j] +=1
            else:
                o[j] = 1
    items = sorted(o.items(), key=lambda x: x[1])  # 按字典的值排序
    for key, value in items:
        ws.append([key, value])
        applyXlsFont(ws)
def fileMantissaOffset(ws):
    print('正在运行尾数偏差追踪表')
    global arrDate, arrSeral
    ws.append([''])
    ws.append([arrDate[startSeral] + arrSeral[startSeral]])
    applyXlsFont(ws)
    mantissaOffset(ws,5)
    mantissaOffset(ws,6)
    mantissaOffset(ws,7)
    mantissaOffset(ws,8)
    mantissaOffset(ws,9)
    mantissaOffset(ws,10)
def fileOmitOffset_blue(ws):
    global arrDate, arrSeral
    ws.append([''])
    ws.append([arrDate[startSeral] + arrSeral[startSeral]])
    applyXlsFont(ws)
    omitOffset_blue(ws, 30)
    # colorOmitOffset(ws, 30)
"8.(中)博彩趋势逆转"
def countOmit(ball, start):
    for i in range(start, 99):
        for j in range(0, 6):
            if redBalls[i][j] == ball:
                return i - start
def trendReverse(ws):
    global arrDate, arrSeral
    ws.append([''])
    ws.append([arrDate[startSeral] + arrSeral[startSeral]])
    applyXlsFont(ws)

    parn = ['' for row in range(6)]
    find = False
    num = 1

    # 筛选最近4期号码, 只查询上期中奖数字
    for i in range(0, 6):
        for j in range(startSeral + 4, startSeral, -1):
            for k in range(0, 6):
                if redBalls[j][k] == redBalls[startSeral][i]:
                    parn[i] += 'x'
                    find = True
                    num = 1
                    break
            if find == False:
                parn[i] += str(num)
                num = num + 1
            find = False
        parn[i] += 'x'
        num = 1

    find = False
    for i in range(0, 6):
        if parn[i] == '123xx':
            om = countOmit(redBalls[startSeral][i], startSeral + 5)
            if om + 3 >= 17:
                ws.append(['博彩逆转：', '数字：' + str(redBalls[startSeral][i]), '遗漏了' + str(om + 3) + '次',
                           ('模式是1..%d ' % (om + 3)) + parn[i][3:5]])
                applyXlsFont(ws)
                find = True
        elif parn[i] == '12x1x':
            om = countOmit(redBalls[startSeral][i], startSeral + 5)
            if om + 2 >= 17:
                ws.append(['博彩逆转：', '数字：' + str(redBalls[startSeral][i]), '遗漏了' + str(om + 2) + '次',
                           ('模式是1..%d ' % (om + 2)) + parn[i][2:5]])
                applyXlsFont(ws)
                find = True
        elif parn[i] == '1x12x':
            om = countOmit(redBalls[startSeral][i], startSeral + 5)
            if om + 1 >= 17:
                ws.append(['博彩逆转：', '数字：' + str(redBalls[startSeral][i]), '遗漏了' + str(om + 1) + '次',
                           ('模式是1..%d ' % (om + 1)) + parn[i][1:5]])
                applyXlsFont(ws)
                find = True
        elif parn[i] == 'x123x':
            om = countOmit(redBalls[startSeral][i], startSeral + 5)
            if om >= 17:
                ws.append(['博彩逆转：', '数字：' + str(redBalls[startSeral][i]), '遗漏了' + str(om) + '次',
                           ('模式是1..%d ' % (om)) + parn[i][:]])
                applyXlsFont(ws)
                find = True
"层叠"
def pile(ws):
    # 只查询中奖的号码
    for num in range(0, 6):
        ser = startSeral + 1
        ball = redBalls[startSeral][num]
        omit = omitTable[ser][ball]
        omit2 = 0
        omit3 = 0
        parn = []
        if omit >= 15:
            # x
            omit2 = omitTable[ser + omit + 1][ball]
            if omit2 == 3:
                # x123x
                parn.append('x123x 1..%d x' % omit)
                ser = ser + 4
                omit2 = omitTable[ser + omit + 1][ball]
            elif omit2 == 2:
                # x12x
                parn.append('x12x 1..%d x' % omit)
                ser = ser + 3
                omit2 = omitTable[ser + omit + 1][ball]
            elif omit2 == 1:
                # x1x
                parn.append('x1x 1..%d x' % omit)
                ser = ser + 2
                omit2 = omitTable[ser + omit + 1][ball]
            elif omit2 == 0:
                # xx
                parn.append('xx 1..%d x' % omit)
                ser = ser + 1
                omit2 = omitTable[ser + omit + 1][ball]
                i = 0
                while omit2 == 0:
                    # xxx..
                    i = i + 1
                    ser = ser + 1
                    omit2 = omitTable[ser + omit + 1][ball]
                    parn.append('x')
            else:
                parn.append('x 1..%d x' % omit)

            if omit2 >= 8 and omit2 <= 15:
                # x
                omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                if omit3 == 3:
                    # x123x
                    ser = ser + 4
                    parn.append('x123x 1..%d ' % omit2)
                    omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                elif omit3 == 2:
                    # x12x
                    ser = ser + 3
                    parn.append('x12x 1..%d ' % omit2)
                    omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                elif omit3 == 1:
                    # x1x
                    ser = ser + 2
                    parn.append('x1x 1..%d ' % omit2)
                    omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                elif omit3 == 0:
                    # xx
                    ser = ser + 1
                    omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                    parn.append('xx 1..%d ' % omit2)
                    i = 0
                    while omit3 == 0:
                        # xxx..
                        i = i + 1
                        ser = ser + 1
                        omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                        parn.append('x')
                else:
                    parn.append('x 1..%d ' % omit2)

                if omit3 >= 3 and omit3 <= 8:
                    parn.append('x 1..%d ' % omit3)
                    parn.reverse()
                    ws.append(['层叠：', '数字：%02d' % (ball), '模式：%s' % ''.join(parn)])
                    applyXlsFont(ws)
"反向层叠"
def rePile(ws):
    # 只查询中奖的号码
    for num in range(0, 6):
        ser = startSeral + 1
        ball = redBalls[startSeral][num]
        omit = omitTable[ser][ball]
        omit2 = 0
        omit3 = 0
        parn = []
        if omit <= 8 and omit >= 3:
            # x
            omit2 = omitTable[ser + omit + 1][ball]
            if omit2 == 3:
                # x123x
                parn.append('x123x 1..%d x' % omit)
                ser = ser + 4
                omit2 = omitTable[ser + omit + 1][ball]
            elif omit2 == 2:
                # x12x
                parn.append('x12x 1..%d x' % omit)
                ser = ser + 3
                omit2 = omitTable[ser + omit + 1][ball]
            elif omit2 == 1:
                # x1x
                parn.append('x1x 1..%d x' % omit)
                ser = ser + 2
                omit2 = omitTable[ser + omit + 1][ball]
            elif omit2 == 0:
                # xx
                parn.append('xx 1..%d x' % omit)
                ser = ser + 1
                omit2 = omitTable[ser + omit + 1][ball]
                i = 0
                while omit2 == 0:
                    # xxx..
                    i = i + 1
                    ser = ser + 1
                    omit2 = omitTable[ser + omit + 1][ball]
                    parn.append('x')
            else:
                parn.append('x 1..%d x' % omit)

            if omit2 >= 8 and omit2 <= 15:
                # x
                omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                if omit3 == 3:
                    # x123x
                    ser = ser + 4
                    parn.append('x123x 1..%d ' % omit2)
                    omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                elif omit3 == 2:
                    # x12x
                    ser = ser + 3
                    parn.append('x12x 1..%d ' % omit2)
                    omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                elif omit3 == 1:
                    # x1x
                    ser = ser + 2
                    parn.append('x1x 1..%d ' % omit2)
                    omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                elif omit3 == 0:
                    # xx
                    ser = ser + 1
                    omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                    parn.append('xx 1..%d ' % omit2)
                    i = 0
                    while omit3 == 0:
                        # xxx..
                        i = i + 1
                        ser = ser + 1
                        omit3 = omitTable[ser + omit + 1 + omit2 + 1][ball]
                        parn.append('x')
                else:
                    parn.append('x 1..%d ' % omit2)

                if omit3 >= 15:
                    parn.append('x 1..%d ' % omit3)
                    parn.reverse()
                    ws.append(['反向层叠：', '数字：%02d' % (ball), '模式：%s' % ''.join(parn)])
                    applyXlsFont(ws)
"n底"
def nBottom(ws):
    # 检验所有数字，预测提示
    for num in range(1, 34):
        ser = startSeral
        ball = num
        cnt = 1
        parn = []
        tRange = -1
        if omitTable[ser][ball] == 0:
            continue

        while True:
            omit = omitTable[ser][ball]
            i = 0
            while omit == 0:
                i = i + 1
                ser = ser + 1
                omit = omitTable[ser][ball]

            omit2 = omitTable[ser + omit + 1][ball]
            j = 0
            while omit2 == 0:
                j = j + 1
                ser = ser + 1
                omit2 = omitTable[ser + omit + 1][ball]

            if tRange == -1:
                tRange = omit

            if (omit >= 2 and omit2 >= 2) and (omit == tRange or omit == tRange + 1 or omit == tRange - 1) and (
                    omit2 == tRange or omit2 == tRange + 1 or omit2 == tRange - 1):
                if cnt == 1:
                    for cnt in range(omit, 0, -1):
                        parn.append(str(cnt))

                while i != 0:
                    parn.append('x')
                    i = i - 1

                parn.append('x')
                cnt = cnt + 1
                ser = ser + omit + 1

                while j != 0:
                    parn.append('x')
                    j = j - 1

                for k in range(omit2, 0, -1):
                    parn.append(str(k))


            else:
                if cnt != 1:
                    parn.append('x')
                    parn.reverse()
                    ws.append(['%d倍底：' % cnt, '数字：%d' % ball, '模式：%s' % ''.join(parn)])
                    applyXlsFont(ws)
                    cnt = 1
                break
"旗式排列"
def flagRange(ws):
    # 检验所有数字，预测提示
    # tips: 利用数组翻转
    for num in range(1, 34):
        ser = startSeral
        ball = num
        parn = []
        isOk = False
        # isOk2 = False
        if omitTable[ser][ball] == 0:
            continue
        isInit = True
        omit = 0
        cnt = 0
        while True:
            omit = omitTable[ser][ball]
            if isInit == True:
                if omit != 5 and omit != 6:
                    isOk = True
                    break

            if omit == 5 or omit == 6:
                ser = ser + omit + 1
                if isInit == False:
                    parn.append('x')
                for j in range(omit, 0, -1):
                    parn.append(str(j))
                if ser > 99:
                    break
                isInit = False
            elif omit < 3:
                # if omit == 3:
                #     #x123x
                #     parn.append('x123x')
                #     ser = ser + omit + 1
                if omit == 2:
                    # x12x
                    parn.append('x12x')
                    ser = ser + omit + 1
                elif omit == 1:
                    # x1x
                    parn.append('x1x')
                    ser = ser + omit + 1
                elif omit == 0:
                    # xx
                    parn.append('x')
                    while omit == 0:
                        ser = ser + 1
                        omit = omitTable[ser][ball]
                        parn.append('x')
                isInit = True
            else:
                break

            cnt = cnt + 1

        if cnt >= 2 and isOk == True:
            parn.reverse()
            ws.append(['旗式排列：', '数字：%d' % ball, '模式：%s' % ''.join(parn)])
            applyXlsFont(ws)
"热门中奖数字 -- 废弃"
def hotNum(ws):
    for i in range(0, 6):
        ser = startSeral + 1
        ball = redBalls[startSeral][i]
        parn = []
        isOk = False
        isOk2 = False
        while True:
            omit = omitTable[ser][ball]
            if omit >= 6:
                isOk = True
                ser = ser + omit + 1
                parn.append('x')
                for j in range(omit, 0, -1):
                    parn.append(str(j))
                if ser > 99:
                    break

            else:
                break

        if isOk:
            while True:
                if omit == 2 or omit == 1:
                    parn.append('x')
                    for j in range(omit, 0, -1):
                        parn.append(str(j))
                    isOk2 = True
                    ser = ser + omit + 1
                    omit = omitTable[ser][ball]
                    if ser > 99:
                        break
                else:
                    if isOk2:
                        parn.append('x')
                        parn.reverse()
                        ws.append(['热门中奖数字：', '数字：%d' % ball, '模式：%s' % ''.join(parn)])
                        applyXlsFont(ws)
                        ws.append([''])
                    break
"9.重复中奖模式表"
def count_consecutive_occurrences(arr, target_number, n):
    counts = np.sum(np.isin(arr, target_number), axis=1)
    consecutive_counts = np.convolve(counts, np.ones(n, dtype=int), mode='valid')
    return np.sum(consecutive_counts == n)
def repeatWinPattern(ws):
    print('正在运行重复中奖模式表')
    global arrDate, arrSeral
    applyXlsFont(ws)
    df_redBalls = pd.DataFrame(redBalls)
    repeat_counts = [0 for j in range(7)]
    for i in range(1,34):
        for k in range(7):
            repeat_counts[k] = count_consecutive_occurrences(df_redBalls,i,k+1)
        rato_repeat = repeat_counts[0]/(repeat_counts[1]+repeat_counts[2]+repeat_counts[3]+repeat_counts[4]+repeat_counts[5]+repeat_counts[6])
        ws.append([i,repeat_counts[0],repeat_counts[1],repeat_counts[2],repeat_counts[3],repeat_counts[4],repeat_counts[5],repeat_counts[6],rato_repeat])
    applyXlsFont(ws)
"10.遗漏与中奖分析表"
def numberOmitCount(df,number,n):
    '''
    统计在pandas结构数据df中数字number隔n次出现的次数
    :param df: pandas结构数据
    :param number: 需要找出的数字
    :param times: 两数的间隔
    :return:
    '''
    if n<0:
        raise ValueError("n 必须为一个正整数")

    count = 0
    for i in range(len(df)-n-1):
        if any(np.isin(df[i],number)) and any(np.isin(df[i+n+1],number)):
            count += 1
    return count
def omitWin(ws):
    global arrDate, arrSeral
    applyXlsFont(ws)
    data = redBalls
    for i in range(1,34):
        content = [i]
        content.append(omitTable[0][i])
        for j in range(35):
            content.append(numberOmitCount(data,i,j))
        ws.append(content)
    titles_blue = ['蓝球待选数字']
    titles_blue.append('当前遗漏')
    for i in range(35):
        titles_blue.append(u'遗漏{}次'.format(i))
    maxColumn = ws.max_column
    for i in range(len(titles_blue)):
        ws.cell(1, maxColumn+i+2).value = titles_blue[i]
    data_blue = [[x] for x in blueBalls] #由于蓝球只有一列，需要转换函数才能运行
    # 计算并添加蓝球遗漏
    for i in range(1,17):
        content_blue = []
        content_blue.append(i)
        content_blue.append(omitTable_blue[0][i])
        for j in range(35):
            content_blue.append(numberOmitCount(data_blue,i,j))
        for k in range(len(content_blue)):
            ws.cell(i+1, maxColumn + k + 2).value = content_blue[k]

"彩票期数表"
def lotteryDraws(ws):
    print('正在运行彩票期数表')
    global arrDate, arrSeral
    ws.append(["彩票期数表"])
    applyXlsFont(ws)
    titles=[' ']
    for i in range(1,34):
        titles.append(u'数字{}'.format(i))
    ws.append(titles)
    RedomitTable = np.where(np.array(omitTable) == 0,'#',np.array(omitTable))
    for i in range(100):
        RedomitTable[i][0]=arrSeral[i]

    RedomitTable = RedomitTable.tolist()
    for i in range(100,-1,-1):
        ws.append(RedomitTable[i])
    BlueomitTable = np.where(np.array(omitTable_blue) == 0,'#',np.array(omitTable_blue))
    for i in range(100):
        BlueomitTable[i][0]=arrSeral[i]
    BlueomitTable = BlueomitTable.tolist()
    ws.append(['蓝球'])
    for i in range(100,-1,-1):
        ws.append(BlueomitTable[i])

"小型彩票期数表"
def littleLotteryDraws(ws):
    print('正在运行小型彩票期数表')
    global arrDate, arrSeral
    titles = ["小型彩票期数表"]
    for i in range(100):
        titles.append(arrSeral[i])
    ws.append(titles)
    applyXlsFont(ws)

    winNumDict = {}
    for j in range(1, 34):
        listNum = ['' for m in range(100)]
        for i in range(100):
            if j in redBalls[i]:
                for k in range(6):
                    if redBalls[i][k] == j:
                        listNum[i] = 'x'
                        winNumDict[redBalls[i][k]] = 1
            elif j in winNumDict.keys():
                listNum[i] = '-'
            else:
                listNum[i] = '='
        listNum.insert(0, u'数字{}'.format(j))
        ws.append(listNum)

"中奖间隔期数表"
def lotteryDrawsInterval(ws):
    print('正在运行中奖间隔期数表')
    global arrDate, arrSeral
    ws.append(["中奖间隔期数表","当前遗漏"])
    applyXlsFont(ws)
    for i in range(1,34):
        interval_red = num_interval(redBalls,i)
        interval_red1 = interval_red.tolist()
        interval_red1.insert(0,u'数字{}'.format(i))
        interval_red1.insert(1,omitTable[0][i])
        ws.append(interval_red1)

    ws.append(["蓝球","当前遗漏"])
    for i in range(1,17):
        interval_blue = num_interval(blueBalls,i)
        interval_blue1 = interval_blue.tolist()
        interval_blue1.insert(0,u'数字{}'.format(i))
        interval_blue1.insert(1,omitTable_blue[0][i])
        ws.append(interval_blue1)
def num_interval(arr,x):
    array = np.array(arr)
    index_positons = np.where(array == x)
    row_distances = np.diff(index_positons[0])
    row_distances -=1
    return row_distances

"伴侣数字表"
def count_same_row(arr,*nums):
    count = 0
    for row in arr:
        if all(x in row for x in nums):
            count +=1
    return count
def count_together_numbers(x, y, data):
    # 使用向量化操作来计算同时包含 x 和 y 的次数
    count = np.sum(np.isin(data, x), axis=1) + np.sum(np.isin(data, y), axis=1)
    count = count == 2
    # 返回满足条件的次数总和
    return np.sum(count)
def CompanionNumOffset(ws):
    print("正在运行伴侣数字表")
    global arrDate, arrSeral
    ws.append(["伴侣数字表"])
    applyXlsFont(ws)

    titles = ['']
    for i in range(1, 34):
        titles.append(u'数字{}'.format(i))
    ws.append(titles)

    result_matrix = [[0 for j in range(1,34)] for i in range(1,34)]
    data = redBalls
    for i in range(1, 34):
        for j in range(1, 34):
            result_matrix[j-1][i-1] = count_together_numbers(i, j, data)
    for j in range(1, 34):
        result_matrix[j-1].insert(0,j)
    for i in result_matrix:
        ws.append(i)
    ws.append(["三个数字一组的伴侣数字"])
    dict_adjace_3 = {}
    for i in range(1, 32):
        for j in range(i+1, 33):
            for k in range(j+1, 34):
                dict_adjace_3[u'{}-{}-{}'.format(i, j, k)] = count_same_row(redBalls, i, j, k)
    sorted_dict_adjace_3 = sorted(dict_adjace_3.items(), key=lambda x: x[1], reverse=True)
    for key, value in sorted_dict_adjace_3:
        if value >10: #只要一起出现大于等于4次的
            ws.append([value,key])
            applyXlsFont(ws)
    ws.append(["四个数字一组的伴侣数字"])
    dict_adjace_4 = {}
    for i in range(1, 31):
        for j in range(i + 1, 32):
            for k in range(j + 1, 33):
                for l in range(k + 1, 34):
                    # if len(set([i,j,k,l]))==4:
                    dict_adjace_4[u'{}-{}-{}-{}'.format(i, j, k, l)] = count_same_row(redBalls, i, j, k,l)
    sorted_dict_adjace_4 = sorted(dict_adjace_4.items(), key=lambda x: x[1], reverse=True)
    for key, value in sorted_dict_adjace_4:
        if value > 5:  # 只要一起出现大于等于3次的
            ws.append([value, key])
            applyXlsFont(ws)
"追踪数字统计表"
def followsNum(arr,num_a,num_b):
    arr_num = np.array(arr)
    n = len(arr_num)
    counts = 0
    # 遍历每一行并判断条件
    for row_index in range(1,n):
        if np.isin(arr_num[row_index],num_a).any() and np.isin(arr_num[row_index-1],num_b).any():
            counts += 1
    return counts
def followsNumoffset(ws):
    print('正在运行追踪数字统计表')
    global arrDate, arrSeral
    ws.append(["追踪数字统计表"])
    winsNum = ['']
    winsNum.extend(redBalls[0])
    winsNum.append('总和')
    ws.append(winsNum)
    applyXlsFont(ws)

    for i in range(1,34):
        contents = []
        for j in range(6):
            wN = redBalls[0][j]
            contents.append(followsNum(redBalls,wN,i))
        total = sum(contents)
        contents.insert(0,u"数字{}".format(i))
        ws.append(contents)
        ws.cell(row=ws.max_row,column=8,value=total)
"邻近数字中奖情况表"
def asAdjanceNum(arr,x):
    arr_num = np.array(arr)
    n = len(arr_num)
    counts = 0
    # 遍历每一行并判断条件
    for row_index in range(1, n):
        if np.isin(arr_num[row_index], x).any() and np.isin(arr_num[row_index - 1], [x-1,x+1]).any():
            counts += 1
    return counts
def AdjanceNumoffset(ws):
    print('正在运行邻近数字中奖情况表')
    global arrDate, arrSeral
    ws.append(["邻近数字中奖情况表"])
    ws.append(["待选数字","总中奖次数","作为上期邻近数字中奖次数","作为上期邻近数字中奖比例"])
    applyXlsFont(ws)
    for i in range(1,34):
        contents = []
        contents.append(u'数字{}'.format(i))
        total_win_times = count_consecutive_occurrences(redBalls,i,1)
        contents.append(total_win_times)
        adjace_win_times = asAdjanceNum(redBalls,i)
        contents.append(adjace_win_times)
        ratio = '{:.2%}'.format(float(adjace_win_times) / float(total_win_times))
        contents.append(ratio)
        ws.append(contents)
    ws.append(["同期邻近数字中奖情况表"])
    ws.append(["2个一起邻近数字中奖情况"])
    dict_adjace_2 = {}
    for i in range(1,33):
        dict_adjace_2[u'{}-{}'.format(i, i+1)] = count_same_row(redBalls, i, i+1)
    sorted_dict_adjace_2 = sorted(dict_adjace_2.items(), key=lambda x: x[1], reverse=True)
    for key, value in sorted_dict_adjace_2:
        if value > 10:  # 只要一起出现大于等于10次的
            ws.append([value, key])
            applyXlsFont(ws)
    ws.append(["3个一起邻近数字中奖情况"])
    dict_adjace_3 = {}
    for i in range(1, 32):
        dict_adjace_3[u'{}-{}-{}'.format(i, i + 1,i+2)] = count_same_row(redBalls, i, i + 1,i+2)
    sorted_dict_adjace_3 = sorted(dict_adjace_3.items(), key=lambda x: x[1], reverse=True)
    for key, value in sorted_dict_adjace_3:
        if value > 5:  # 只要一起出现大于等于2次的
            ws.append([value, key])
            applyXlsFont(ws)
    ws.append(["4个一起邻近数字中奖情况"])
    dict_adjace_4 = {}
    for i in range(1, 31):
        dict_adjace_4[u'{}-{}-{}-{}'.format(i, i + 1, i + 2,i+3)] = count_same_row(redBalls, i, i + 1, i + 2,i+3)
    sorted_dict_adjace_4 = sorted(dict_adjace_4.items(), key=lambda x: x[1], reverse=True)
    for key, value in sorted_dict_adjace_4:
        if value > 0:  # 只要一起出现大于等于2次的
            ws.append([value, key])
            applyXlsFont(ws)
"中奖数字情况表"
def winningNumTable(ws):
    print('正在运行中奖数字情况表')
    global arrDate, arrSeral
    ws.append(["中奖数字情况表"])
    ws.append(["待选数字", "总中奖次数"])
    applyXlsFont(ws)
    ws.append([" ","最近5期","最近10期","最近25期","最近50期","最近2000期"])
    for i in range(1,34):
        ws.append([i,np.count_nonzero(np.array(redBalls[:5]) == i),np.count_nonzero(np.array(redBalls[:10]) == i),np.count_nonzero(np.array(redBalls[:25]) == i),np.count_nonzero(np.array(redBalls[:50]) == i),np.count_nonzero(np.array(redBalls) == i)])
"创建新表"
def createXlsx():
    wb = Workbook()

    # ws0 = wb.create_sheet("0")
    ws0 = wb.active
    ws0.title = u'文件信息'
    ws0['A1'] = u'最新更新期数'

    ws1 = wb.create_sheet("1")
    ws1.title = u'奇偶数表'

    ws2 = wb.create_sheet("2")
    ws2.title = u'大小数表'

    ws3 = wb.create_sheet("3")
    ws3.title = u'和值偏差表'
    ws3.append(
        ['日期', '期数', '和值', '020', '030', '040', '050', '060', '070', '080', '090', '100', '110', '120', '130',
         '140', '150', '160', '170', '180', '190'])
    applyXlsFont(ws3)

    ws4 = wb.create_sheet("4")
    ws4.title = u'区间表'
    ws4.append(['日期', '期数', '1-5', '6-10', '11-15', '16-20', '21-25', '26-30', '31-33','蓝球1-4','蓝球5-8','蓝球9-12','蓝球13-16','1-8','9-16','17-24','25-33'])
    applyXlsFont(ws4)

    ws5 = wb.create_sheet("5")
    ws5.title = u'百分比表'
    ws5.append(['日期', '期数', '热号', '冷号', '温号','热号中奖个数','温号中奖个数','冷号中奖个数', '蓝球热号', '蓝球冷号', '蓝球温号','蓝球热号中奖个数','蓝球温号中奖个数','蓝球冷号中奖个数','最近10期出现2次以上及最近5期出现1次及以上号码','红球中奖在前列中的个数'])
    applyXlsFont(ws5)

    ws6 = wb.create_sheet("6")
    ws6.title = u'遗漏表'
    ws6.append(['日期', '期数', '中奖号码', '遗漏情况', '遗漏少于10次的个数', '总计', '平均', '蓝球遗漏情况'])
    applyXlsFont(ws6)

    ws7 = wb.create_sheet("7")
    ws7.title = u'遗漏数字偏差表'

    ws8 = wb.create_sheet("8")
    ws8.title = u'蓝球遗漏数字偏差表'

    ws9 = wb.create_sheet("9")
    ws9.title = u'尾数偏差追踪表'

    ws10 = wb.create_sheet("10")
    ws10.title = u'中期表'

    ws12 = wb.create_sheet("12")
    ws12.title = u'重复中奖模式表'
    ws12.append(['待选数字', '中奖1次', '中奖2次', '中奖3次', '中奖4次', '中奖5次', '中奖6次', '中奖7次','双倍中奖比率'])

    ws11 = wb.create_sheet("11")
    ws11.title = u'遗漏与中奖分析表'
    titles = ['待选数字']
    titles.append('当前遗漏')
    for i in range(35):
        titles.append(u'遗漏{}次'.format(i))
    ws11.append(titles)

    ws13 = wb.create_sheet("13")
    ws13.title = u'伴侣数字表'

    ws14 = wb.create_sheet("14")
    ws14.title = u'追踪数字统计表'

    ws15 = wb.create_sheet("15")
    ws15.title = u'邻近数字中奖情况表'

    ws16 = wb.create_sheet("16")
    ws16.title = u'彩票期数表'

    ws17 = wb.create_sheet("17")
    ws17.title = u'小型彩票期数表'

    ws18 = wb.create_sheet("18")
    ws18.title = u'中奖间隔期数表'

    ws19 = wb.create_sheet("19")
    ws19.title = u'中奖数字情况表'
    return wb

"完整性检查"
def checkComplete(ws):
    global arrDate, arrSeral, startSeral
    v = ws['B1'].value
    if v == arrSeral[0]:
        print('no need to update.')
        exit()
    elif v == arrSeral[1]:
        startSeral = 0
        print('update the newest.')
        return True
    else:
        for i in range(2, 100):
            if v == arrSeral[i]:
                startSeral = i - 1  # 从下一期开始更新
                print('update from %s' % arrSeral[startSeral])
                break
        return False
def addInfo(ws):
    ws['B1'] = arrSeral[0]
def countBall():
    # fileOddOrEven(wb['奇偶数表'])
    # fileBigOrSmall(wb['大小数表'])
    # sumOffset(wb['和值偏差表'])
    ballRange(wb['区间表'])
    # hotOrCold(wb['百分比表'])
    # fileOmit(wb['遗漏表'])
    # fileOmitOffset(wb['遗漏数字偏差表'])
    # fileMantissaOffset(wb['尾数偏差追踪表'])
    # fileOmitOffset_blue(wb['蓝球遗漏数字偏差表'])
    #
    # trendReverse(wb['中期表'])
    # pile(wb['中期表'])
    # rePile(wb['中期表'])
    # nBottom(wb['中期表'])
    # flagRange(wb['中期表'])
    # hotNum(wb['中期表'])
if __name__ == '__main__':
    startTime = time.time()

    exist = os.path.isfile('./myf_data.xlsx')
    getData(2000)
    if exist:
        print('load exsit file.')
        wb = load_workbook('./myf_data.xlsx')
        if checkComplete(wb['文件信息']):
            countBall()
            winningNumTable(wb['中奖数字情况表'])
            repeatWinPattern(wb['重复中奖模式表'])
            omitWin(wb['遗漏与中奖分析表'])
            lotteryDraws(wb['彩票期数表'])
            littleLotteryDraws(wb['小型彩票期数表'])
            CompanionNumOffset(wb['伴侣数字表'])
            lotteryDrawsInterval(wb['中奖间隔期数表'])
            followsNumoffset(wb['追踪数字统计表'])
            AdjanceNumoffset(wb['邻近数字中奖情况表'])
        else:
            while (startSeral != -1):
                countBall()
                startSeral = startSeral - 1
            winningNumTable(wb['中奖数字情况表'])
            repeatWinPattern(wb['重复中奖模式表'])
            omitWin(wb['遗漏与中奖分析表'])
            lotteryDraws(wb['彩票期数表'])
            littleLotteryDraws(wb['小型彩票期数表'])
            CompanionNumOffset(wb['伴侣数字表'])
            lotteryDrawsInterval(wb['中奖间隔期数表'])
            followsNumoffset(wb['追踪数字统计表'])
            AdjanceNumoffset(wb['邻近数字中奖情况表'])
    else:
        print('create new file.')
        wb = createXlsx()
        startSeral = 49
        while (startSeral != -1):
            countBall()
            startSeral = startSeral - 1
        # winningNumTable(wb['中奖数字情况表'])
        # repeatWinPattern(wb['重复中奖模式表'])
        # omitWin(wb['遗漏与中奖分析表'])
        # lotteryDraws(wb['彩票期数表'])
        # littleLotteryDraws(wb['小型彩票期数表'])
        # CompanionNumOffset(wb['伴侣数字表'])
        # lotteryDrawsInterval(wb['中奖间隔期数表'])
        # followsNumoffset(wb['追踪数字统计表'])
        # AdjanceNumoffset(wb['邻近数字中奖情况表'])
    addInfo(wb['文件信息'])
    wb.save('./myf_data.xlsx')
    endTime = time.time()
    print("总共运行{}s".format(endTime-startTime))

